create database ServiceJsp
go
use ServiceJsp
go
create table staff(
	id int identity(1,1),
	username nvarchar(30) unique,
	password nvarchar(30),
	primary key(id)
)
go
--drop table patient
--drop table info

create table patient(
	id int identity(1,1),
	username nvarchar(30) unique,
	password nvarchar(30),
	[name] nvarchar(30),
	age int,
	primary key(id)
)
go
--select * from patient
create table info(
	id int identity(1,1),
	patientid int,
	sick nvarchar(30),
	hospital nvarchar(30),
	treatment text,
	primary key (id),
	FOREIGN KEY (patientid) references patient(id)
)
go
--drop trigger InsertStaff
--create trigger InsertStaff
--On staff 
--FOR INSERT,UPDATE
--AS 
----	Declare @username nvarchar(30),
----			@password nvarchar(30)
----	SELECT @username = username,@password = password from inserted
--	UPDATE inserted set password = HashBytes('MD5',password)
--	--INSERT INTO staff VALUES(@username,HashBytes('MD5',@password))
--go
--------------
go
create proc search_info
	@patientid int
as
	select * from info where patientid = @patientid
go
exec search_info 1
go
create proc insert_patient
	@username nvarchar(30),
	@password nvarchar(30),
	@name nvarchar(30),
	@age int
as
	insert into patient values(@username,HashBytes('MD5',@password),@name,@age)
go
exec insert_patient 'ducnt','123456','duc bim','22'
go
create proc insert_info
	@patientid int,
	@sick nvarchar(30),
	@hospital nvarchar(30),
	@treatment text
as
	insert into info values(@patientid,@sick,@hospital,@treatment)	
go
exec insert_info 1 ,'phat ban' , 'dong y','vacin B'
exec insert_info 1 ,'man ngua' , 'da lieu','vacin B'
exec insert_info 2 ,'soi' , 'Nhi HN','vacin B'
go
--drop proc search_patient
create proc search_patient
	@name nvarchar(30)
as
	select * from patient where [name] like '%'+@name+'%'
go

create proc insert_staff
	@username nvarchar(30),
	@password nvarchar(30)
as
	insert into staff values(@username,HashBytes('MD5',@password))
go
create proc dologin
	@username nvarchar(30),
	@password nvarchar(30)
as
	select * from staff where username = @username and password = HashBytes('MD5',@password)
go

insert into patient values('havn','123456','vu ngoc ha',22)
insert into patient values('dungnt','123456','ngo thieu duc',22)
insert into info values(1,'stomatch','108','aspirin')
exec dologin 'admin','123456'
exec insert_staff 'admin','123456'
select * from info
exec search_patient 'ha'
